var models = require('../db');

var express = require('express');
var router = express.Router();
var mysql = require('mysql');
var $sql = require('../sql');
const { jsonWrite, getJsonData, aryJoinAry, nowInDateBetween, handlePriority, handleDatabaseResponse, handleQuery } = require('../utils/index');
// const $utils = require('../utils/index')
//连接数据库-链接池
var pool = mysql.createPool(models.mysql);

//获取首页导航列表数据
router.post('/getNavigationList', (req, res) => {

  let params = { ...req.body };
  let selectParams = [];

  var sql3 = "";

  sql3 = " \
    SELECT a1.* \
    ,(select id from todaycast.categorys c where c.className=a1.className limit 0,1) classNameID \
    ,(select pathName from todaycast.categorys c where c.className=a1.className limit 0,1) pathName \
    ,( SELECt GROUP_CONCAT(c.className) FROM todaycast.categorys c where c.classNameID=(select id from todaycast.categorys c where c.className=a1.className limit 0,1)  ) childClassNameAll \
    ,( SELECt GROUP_CONCAT(c.pathName) FROM todaycast.categorys c where c.classNameID=(select id from todaycast.categorys c where c.className=a1.className limit 0,1)  ) childPathNameAll \
    ,(select orderNum from todaycast.categorys c where c.className=a1.className limit 0,1) orderNum \
    FROM todaycast.broadcasts a1 \
    INNER JOIN \
    (SELECT a.className,a.id FROM todaycast.broadcasts a LEFT JOIN todaycast.broadcasts b \
   ON a.className=b.className AND a.id<=b.id \
     AND a.isBanner=0 and a.isShow=1 \
    GROUP BY a.className,a.id ";
  if (params.className != "推荐") {
    sql3 += "\
        HAVING COUNT(b.id)<="+ params.pageSize + " ";
    selectParams.push(params.pageSize);
  }
  sql3 += "\
    )b1 \
    ON a1.className=b1.className AND a1.id=b1.id \
    where 1=1 and a1.isBanner=0 and a1.isShow=1 ";

  if (params.className) {
    sql3 += " and a1.className=(select className from todaycast.categorys where pathName=? limit 0,1) ";
    selectParams.push(params.className);
  }

  if (params.searchMsg) {

    sql3 += " and (a1.LabelName like ?";
    selectParams.push("%" + params.searchMsg + "%");
    sql3 += " or a1.hostName like ?";
    selectParams.push("%" + params.searchMsg + "%");
    sql3 += " or a1.liveContent like ? )";
    selectParams.push("%" + params.searchMsg + "%");

  }

  sql3 += "order by a1.isPriority desc, a1.priority asc, a1.createTime desc "


  console.log(sql3, selectParams)
  pool.query(sql3, selectParams, function (err, result) {
    console.log(result)
    var listData = []

    var menuList1 = []
    var menuList2 = []
    result?.forEach(item => {
      if (menuList1.indexOf(item.className) < 0) {

        var childClassNameList = [];
        if (item.childClassNameAll != null) {
          childClassNameList = item.childClassNameAll.split(',')
        }
        var childPathNameList = [];
        if (item.childPathNameAll != null) {
          childPathNameList = item.childPathNameAll.split(',')
        }

        menuList1.push(item.className)
        menuList2.push({
          className: item.className,
          classNameID: item.classNameID,
          pathName: item.pathName,
          orderNum: item.orderNum,
          childClassNameList: aryJoinAry(childClassNameList, childPathNameList),
          dataList: []
        })
      }
    })

    //console.log(menuList2)
    if (menuList2 && menuList2.length > 0) {
      for (let i = 0; i < menuList2.length; i++) {

        if (result && result.length > 0) {
          for (let j = 0; j < result.length; j++) {
            if (result[j].liveStartTime != null && result[j].liveStartTime != "None" && result[j].liveEndTime != null && result[j].liveEndTime != "None") {
              result[j].isLiveing = nowInDateBetwen(result[j].liveStartTime, result[j].liveEndTime);
            }
            else {
              result[j].isLiveing = false
            }

            if (menuList2[i].className == result[j].className) {

              menuList2[i].dataList.push({
                id: result[j].id,
                className: result[j].className,
                hostName: result[j].hostName,
                labelName: result[j].labelName,
                liveLink: result[j].liveLink,
                imgBase: result[j].imgBase,
                liveContent: result[j].liveContent,
                liveSource: result[j].liveStartTime,
                createTime: result[j].createTime,
                OPID: result[j].OPID,
                OPName: result[j].OPName,
                isShow: result[j].isShow,
                hits: result[j].hits,
                isPriority: result[j].isPriority,
                priority: result[j].priority,
                isBanner: result[j].isBanner,
                childClassName: [],
                liveStartTime: result[j].liveStartTime,
                liveEndTime: result[j].liveEndTime,
                hosthead: result[j].hosthead,
                isLiveing: result[j].isLiveing
              })

            }
          }
        }

      }
    }

    //按日期排序
    menuList2.sort(function (a, b) {
      return a.orderNum - b.orderNum;
    })
    //console.log(menuList2)
    // //console.log(menuList1)
    jsonWrite(res, menuList2, err);
  })
});

//获取更多列表数据
router.post('/getMoreList', (req, res) => {


  let params = { ...req.body };
  let selectParams = [];

  var sql2 = "";
  var sql3 = "";

  sql3 = " \
  select b.* \
,(select pathName from todaycast.categorys a where a.id=b.classNameID limit 0,1) pathName \
,(select pathName from todaycast.categorys a where a.id=b.childClassNameID limit 0,1) childPathName \
,( SELECt GROUP_CONCAT(c.className) FROM todaycast.categorys c where c.classNameID=b.classNameID) childClassNameAll  \
,( SELECt GROUP_CONCAT(c.pathName) FROM todaycast.categorys c where c.classNameID=b.classNameID) childPathNameAll  \
from todaycast.broadcasts b \
where 1=1 \
and b.isShow=1  \
and b.isBanner=0";
  if (params.className) {
    sql3 += " and (";
    sql3 += " b.classNameID=(select id from todaycast.categorys a where a.pathName=? limit 0,1)";
    selectParams.push(params.className);

    sql3 += " or b.childClassNameID=(select id from todaycast.categorys a where a.pathName=? limit 0,1) ";
    selectParams.push(params.className);

    sql3 += " )";
  }

  if (params.searchMsg) {

    sql3 += " and (b.LabelName like ?";
    selectParams.push("%" + params.searchMsg + "%");
    sql3 += " or b.hostName like ?";
    selectParams.push("%" + params.searchMsg + "%");
    sql3 += " or b.liveContent like ? )";
    selectParams.push("%" + params.searchMsg + "%");

  }
  sql3 += " order by b.isPriority desc, b.priority asc, b.createTime desc ";

  if (params.pageNum && params.pageSize) {//开启分页
    let current = params.pageNum;//当前页码
    let pageSize = params.pageSize;//一页展示多少条数据
    sql3 += " limit ?,? ";
    selectParams.push((current - 1) * pageSize, parseInt(pageSize));
  }


  sql2 = " \
  select count(*) totalPages from todaycast.broadcasts b \
where 1=1 \
and b.isShow=1  \
and b.isBanner=0";
  if (params.className) {
    sql2 += " and (";
    sql2 += " b.classNameID=(select id from todaycast.categorys a where a.pathName=? limit 0,1)";
    selectParams.push(params.className);

    sql2 += " or b.childClassNameID=(select id from todaycast.categorys a where a.pathName=? limit 0,1) ";
    selectParams.push(params.className);

    sql2 += " )";
  }

  if (params.searchMsg) {

    sql2 += " and (b.LabelName like ?";
    selectParams.push("%" + params.searchMsg + "%");
    sql2 += " or b.hostName like ?";
    selectParams.push("%" + params.searchMsg + "%");
    sql2 += " or b.liveContent like ? )";
    selectParams.push("%" + params.searchMsg + "%");

  }

  pool.query(sql3 + "; " + sql2, selectParams, function (err, resultList) {

    var menuList1 = []
    var menuList2 = []
    if (resultList && resultList.length > 0) {

      var result = resultList[0];
      for (let i = 0; i < result.length; i++) {
        if (menuList1.indexOf(result[i].className) < 0) {

          var childClassNameList = [];
          if (result[i].childClassNameAll != null) {
            childClassNameList = result[i].childClassNameAll.split(',')
          }
          var childPathNameList = [];
          if (result[i].childPathNameAll != null) {
            childPathNameList = result[i].childPathNameAll.split(',')
          }

          menuList1.push(result[i].className)
          menuList2.push({
            className: result[i].className,
            classNameID: result[i].classNameID,
            pathName: result[i].pathName,
            childClassNameList: aryJoinAry(childClassNameList, childPathNameList),
            dataList: []
          })
        }
      }

      for (let i = 0; i < menuList2.length; i++) {

        var rowList1 = []
        var rowList2 = []
        for (let j = 0; j < result.length; j++) {
          if (result[j].liveStartTime != null && result[j].liveStartTime != "None" && result[j].liveEndTime != null && result[j].liveEndTime != "None") {
            result[j].isLiveing = nowInDateBetwen(result[j].liveStartTime, result[j].liveEndTime);
          }
          else {
            result[j].isLiveing = false
          }

          if (menuList2[i].className == result[j].className) {

            if (rowList1.indexOf(result[j].childClassName) < 0) {
              rowList1.push(result[j].childClassName);
              rowList2.push({
                className: result[j].className,
                childClassName: result[j].childClassName,
                childPathName: result[j].childPathName,
                dataList: []
              });
            }
          }
        }

        for (let j = 0; j < result.length; j++) {
          for (let k = 0; k < rowList2.length; k++) {
            if (rowList2[k].childClassName == result[j].childClassName) {
              rowList2[k].dataList.push({
                id: result[j].id,
                className: result[j].className,
                hostName: result[j].hostName,
                labelName: result[j].labelName,
                liveLink: result[j].liveLink,
                imgBase: result[j].imgBase,
                liveContent: result[j].liveContent,
                liveSource: result[j].liveStartTime,
                createTime: result[j].createTime,
                OPID: result[j].OPID,
                OPName: result[j].OPName,
                isShow: result[j].isShow,
                hits: result[j].hits,
                isPriority: result[j].isPriority,
                priority: result[j].priority,
                isBanner: result[j].isBanner,
                childClassName: [],
                liveStartTime: result[j].liveStartTime,
                liveEndTime: result[j].liveEndTime,
                hosthead: result[j].hosthead,
                isLiveing: result[j].isLiveing
              })
            }
          }
          menuList2[i].dataList = rowList2
        }

      }

      //按日期排序
      menuList2.sort(function (a, b) {
        return a.classNameID - b.classNameID;
      })
      //console.log(menuList2)
      jsonWrite(res, menuList2, err, resultList[1][0].totalPages);
    }
    else {

      jsonWrite(res, menuList2, err, 0);
    }
  })

});

//获取后端列表数据
router.post('/getDataList', (req, res) => {

  var sql = $sql.projectSql.getDataList;
  let sql2 = $sql.projectSql.getCount;
  let params = { ...req.body };
  let selectParams = [];

  sql += " where 1=1 ";
  sql2 += " where 1=1 ";

  if (params.className) {
    sql += " and className=?";
    selectParams.push(params.className);
  }
  if (params.childClassName) {
    sql += " and childClassName=?";
    selectParams.push(params.childClassName);
  }
  if (params.labelName) {
    sql += " and labelName=?";
    selectParams.push(params.labelName);
  }
  if (params.liveSource) {
    sql += " and liveSource=?";
    selectParams.push(params.liveSource);
  }
  if (params.hostName) {
    sql += " and hostName like ?";
    selectParams.push("%" + params.hostName + "%");
  }
  if (params.startDate) {
    sql += " and createTime>=?";
    selectParams.push(params.startDate);
  }
  if (params.endDate) {
    sql += " and createTime<=?";
    selectParams.push(params.endDate);
  }
  if (params.isShow === 0 || params.isShow === 1) {
    sql += " and isSHow=?";
    selectParams.push(params.isShow);
  }
  if (params.isBanner === 0 || params.isBanner === 1) {
    sql += " and isBanner=?";
    selectParams.push(params.isBanner);
  }

  sql += " order by createTime desc";//排序为按照创建时间倒叙

  if (params.pageNum || params.pageSize) {//开启分页
    let current = params.pageNum;//当前页码
    let pageSize = params.pageSize;//一页展示多少条数据
    sql += " limit ?,?  ";
    selectParams.push((current - 1) * pageSize, parseInt(pageSize));
  }

  if (params.className) {
    sql2 += " and className=?";
    selectParams.push(params.className);
  }
  if (params.childClassName) {
    sql2 += " and childClassName=?";
    selectParams.push(params.childClassName);
  }
  if (params.labelName) {
    sql2 += " and labelName=?";
    selectParams.push(params.labelName);
  }
  if (params.liveSource) {
    sql2 += " and liveSource=?";
    selectParams.push(params.liveSource);
  }
  if (params.hostName) {
    sql2 += " and hostName like ?";
    selectParams.push("%" + params.hostName + "%");
  }
  if (params.startDate) {
    sql2 += " and createTime>=?";
    selectParams.push(params.startDate);
  }
  if (params.endDate) {
    sql2 += " and createTime<=?";
    selectParams.push(params.endDate);
  }
  if (params.isShow === 0 || params.isShow === 1) {
    sql2 += " and isSHow=?";
    selectParams.push(params.isShow);
  }
  if (params.isBanner === 0 || params.isBanner === 1) {
    sql2 += " and isBanner=?";
    selectParams.push(params.isBanner);
  }

  pool.query(sql + ";" + sql2, selectParams, function (err, result) {
    jsonWrite(res, result, err);
  })
});

// 假设 $sql.projectSql.addLiveInfo 是一个预定义的SQL模板字符串  



// 封装一个函数来构建参数数组  
function buildParams(params) {
  const { isPriority, priority } = handlePriority(params);
  const addParams = [
    params.className,
    params.classNameID,
    params.childClassName,
    params.childClassNameID,
    params.hostName,
    params.hosthead,
    params.labelName,
    params.liveLink,
    params.imgBase,
    params.liveContent,
    params.liveSource,
    params.liveStartTime,
    params.liveEndTime,
    params.OPID,
    params.OPName,
    params.isShow,
    0, // 这个0似乎是一个硬编码的值，可能需要根据实际情况来决定是否应该硬编码  
    isPriority,
    priority,
    params.isBanner
  ];
  return addParams;
}

//新增item--已优化
router.post('/addLiveInfo', (req, res) => {
  // 获取请求体中的参数  
  const params = { ...req.body };

  // 构建参数数组  
  const addParams = buildParams(params);

  // 执行数据库查询  
  pool.query($sql.projectSql.addLiveInfo, addParams, (err, result) => {
    // 处理数据库查询的响应  
    handleDatabaseResponse(res, result, err);
  });
});


//更新item
// 封装一个函数来构建参数数组  
function buildUpdateParams(params) {
  const { isPriority, priority } = handlePriority(params);
  const updateParams = [
    params.className,
    params.classNameID,
    params.childClassName,
    params.childClassNameID,
    params.hostName,
    params.hosthead,
    params.labelName,
    params.liveLink,
    params.imgBase,
    params.liveContent,
    params.liveSource,
    params.liveStartTime,
    params.liveEndTime,
    params.OPID,
    params.OPName,
    params.isShow,
    isPriority,
    priority,
    params.isBanner,
    params.id
  ];
  return updateParams;
}

// 路由处理函数  
router.post('/updateLiveInfo', (req, res) => {
  // 获取请求体中的参数，并进行适当的验证和清理  
  const params = { ...req.body };
  validateAndUpdateParams(params); // 假设这个函数进行参数验证和清理  

  // 构建参数数组  
  const updateParams = buildUpdateParams(params);

  // 执行数据库更新操作  
  pool.query($sql.projectSql.updateLiveInfo, updateParams, (err, result) => {
    if (err) {
      // 如果有错误，返回错误响应  
      jsonWrite(res, null, err);
    } else {
      // 如果没有错误，返回成功响应  
      jsonWrite(res, result, null);
    }
  });
});

// 假设的函数，用于验证和更新参数，具体实现需要根据实际业务逻辑来编写  
function validateAndUpdateParams(params) {
  // 在这里添加参数验证逻辑，例如检查是否所有必填字段都已填写，数据类型是否正确等  
  // 还可以对参数进行清理，例如去除HTML标签、转义特殊字符等，以防止SQL注入等安全问题  
  // 如果验证失败，可以抛出错误或返回错误信息  
}

//删除item
router.post('/deleteLiveInfo', (req, res) => {
  var sql = $sql.projectSql.deleteLiveInfo;
  var params = { ...req.body };

  pool.query(sql, [params.id], function (err, result) {
    jsonWrite(res, result, err);
  })
});

//记录点击量
router.post('/recordHits', (req, res) => {
  let sql = $sql.projectSql.recordHits;
  let sql2 = $sql.projectSql.visitSource;
  let params = { ...req.body };

  var ip = params.ip.split(',')[0].split(':')[1];
  var nationality = params.ip.split(',')[1].split(':')[1];
  var area = params.ip.split(',')[2].split(':')[1];
  var deviceVersion = params.ip.split(',')[3].split(':')[1];

  let hitsParams = [params.id, params.id, params.labelName, ip, nationality, area, deviceVersion, '查看了1次'];

  pool.query(sql + sql2, hitsParams, function (err, result) {

    jsonWrite(res, result, err);
  })

});

//获取分类--已优化
router.post('/getCategory', (req, res) => {
  const params = { ...req.body };
  let sql = $sql.projectSql.getCategory;
  let selectParams = [];

  // 构造查询条件  
  let whereClause = ' WHERE 1=1';
  if (params.className) {
    whereClause += ' AND (CategoryID = ? OR classNameID = ?)';
    selectParams.push(params.classNameID, params.classNameID);
  }
  if (params.childClassName) {
    whereClause += ' AND (CategoryID = ? OR CategoryID = ?)';
    selectParams.push(params.classNameID, params.childClassNameID);
  }

  // 构造完整的SQL语句  
  sql += whereClause + ' ORDER BY CategoryID ASC';

  // 执行数据库查询  
  pool.query(sql, selectParams, function (err, result) {
    if (err) {
      // 处理数据库查询错误  
      jsonWrite(res, null, err);
      return;
    }

    const menuMap = {}; // 用于构建菜单项的映射  
    const menuList = []; // 最终的菜单列表  

    // 遍历查询结果，构建菜单项的映射  
    result.forEach(item => {
      if (item.classNameID === -1) {
        menuMap[item.id] = { ...item, childMenuData: [] };
      } else {
        if (!menuMap[item.parentIndex]) {
          menuMap[item.parentIndex] = { id: item.parentIndex, childMenuData: [] };
        }
        menuMap[item.parentIndex].childMenuData.push({ ...item });
      }
    });

    // 从映射中提取最终的菜单列表  
    for (const id in menuMap) {
      if (menuMap.hasOwnProperty(id)) {
        menuList.push(menuMap[id]);
      }
    }

    // 按日期排序  
    menuList.sort((a, b) => a.orderNum - b.orderNum);

    // 响应结果  
    jsonWrite(res, menuList, null);
  });
});

//添加分类--已优化
router.post('/addCategoryInfo', (req, res) => {
  // 校验参数是否存在  
  const { className, pathName, OPID, OPName, hasParent, classNameID: rawClassNameID, orderNum } = req.body;
  if (!className || !pathName || !OPID || !OPName || !orderNum) {
    return jsonWrite(res, null, 'Missing required parameters');
  }

  // 根据 hasParent 设置 classNameID，默认为 -1  
  const classNameID = hasParent ? rawClassNameID : -1;

  // 准备参数数组，注意顺序要与SQL语句中的占位符顺序相匹配  
  const addParams = [
    className,
    pathName,
    OPID,
    OPName,
    classNameID,
    orderNum
  ];

  // SQL语句（确保$sql.projectSql.addCategoryInfo中有正确数量的占位符）  
  const sql = $sql.projectSql.addCategoryInfo;

  // 执行数据库查询  
  pool.query(sql, addParams, function (err, result) {
    if (err) {
      // 处理数据库查询错误  
      return jsonWrite(res, null, 'Database error: ' + err.message);
    }

    // 响应结果  
    jsonWrite(res, result, null);
  });
});
//修改分类--已优化
router.post('/updateCategoryInfo', (req, res) => {
  const {
    className,
    pathName,
    OPID,
    OPName,
    hasParent,
    classNameID: rawClassNameID,
    orderNum,
    id
  } = req.body;

  // 参数校验  
  if (!className || !pathName || !OPID || !OPName || !orderNum || !id) {
    return jsonWrite(res, null, 'Missing required parameters');
  }

  // 根据 hasParent 设置 classNameID，默认为 -1  
  const classNameID = hasParent ? rawClassNameID : -1;

  // 准备参数数组，确保顺序与SQL语句中的占位符顺序相匹配  
  const editParams = [
    className,
    pathName,
    OPID,
    OPName,
    classNameID,
    orderNum,
    id
  ];

  // SQL语句（确保$sql.projectSql.editCategoryInfo中有正确数量的占位符）  
  const sql = $sql.projectSql.editCategoryInfo;

  // 执行数据库查询  
  pool.query(sql, editParams, function (err, result) {
    if (err) {
      // 数据库查询错误处理  
      return jsonWrite(res, null, 'Database error: ' + err.message);
    }

    // 响应结果  
    jsonWrite(res, result, null);
  });
});
//删除分类--已优化
router.post('/deleteCategoryInfo', (req, res) => {
  // 从请求体中提取需要的参数  
  const { id } = req.body;

  // 参数校验  
  if (!id) {
    return jsonWrite(res, null, 'Missing required parameter: id');
  }

  // 准备参数数组  
  const deleteParams = [id];

  // SQL语句（确保$sql.projectSql.deleteCategoryInfo中有正确的占位符）  
  const sql = $sql.projectSql.deleteCategoryInfo;

  // 执行数据库查询  
  pool.query(sql, deleteParams, function (err, result) {
    if (err) {
      // 数据库查询错误处理  
      return jsonWrite(res, null, 'Database error: ' + err.message);
    }

    // 响应结果  
    jsonWrite(res, result, null);
  });
});


// 获取平台  --已优化
router.get('/getLiveSource', async (req, res) => {
  const sql = $sql.projectSql.getLiveSource;
  await handleQuery(req, res, sql);
});

// 获取标签  --已优化
router.post('/getLiveLabel', async (req, res) => {
  const sql = $sql.projectSql.getLiveLabel;
  await handleQuery(req, res, sql);
});

// 获取banner横幅  --已优化
router.get('/getBanner', async (req, res) => {
  const sql = $sql.projectSql.getBanner;
  await handleQuery(req, res, sql);
});

// 获取日访问量  --已优化
router.post('/getVisitsDay', async (req, res) => {
  try {
    const params = { ...req.body };
    const sql = $sql.projectSql.getVisitsDay;

    // 初始化查询条件数组  
    const conditions = [];
    const searchData = [];

    // 添加固定的查询条件  
    conditions.push("nationality is not null");

    // 根据startDate添加查询条件  
    if (params.startDate) {
      conditions.push("visitTime >= ?");
      searchData.push(params.startDate);
    }

    // 根据endDate添加查询条件  
    if (params.endDate) {
      conditions.push("visitTime <= ?");
      searchData.push(params.endDate);
    }

    // 根据labelName数组添加查询条件  
    if (params.labelName && params.labelName.length > 0) {
      const placeholders = params.labelName.map(() => '?').join(',');
      conditions.push(`labelName IN (${placeholders})`);
      searchData.push(...params.labelName);
    }

    // 拼接完整的SQL语句  
    const finalSql = `${sql} WHERE ${conditions.join(' AND ')} GROUP BY DATE_FORMAT(visitTime,'%Y-%m-%d'), labelName`;

    // 执行查询  
    const result = await pool.query(finalSql, searchData);

    // 处理结果并返回  
    const resultData = getJsonData(result);
    jsonWrite(res, resultData, null);
  } catch (err) {
    // 发生错误时返回错误信息  
    jsonWrite(res, null, err);
  }
});

// 获取总访问量，独立ip数  
router.post('/getVisitsTotal', async (req, res) => {
  const sql = $sql.projectSql.getVisitsTotal;
  await handleQuery(req, res, sql);
});

// 获取月访问量  
router.post('/getVisitDayLabel', async (req, res) => {
  const sql = $sql.projectSql.getVisitDayLabel;
  await handleQuery(req, res, sql);
});



// 获取新闻列表
router.post("/getNewsList", (req, res) => {
  let sql = $sql.projectSql.getNewsList;
  let sql2 = $sql.projectSql.getNewsCount;

  sql += " where 1=1 ";
  sql2 += " where 1=1 ";

  console.log(sql)
  let params = { ...req.body };
  let selectParams = [];

  if (params.maintitle) {
    sql += " and maintitle like ?";
    selectParams.push("%" + params.maintitle + "%");
  }
  if (params.subtitle) {
    sql += " and subtitle like ?";
    selectParams.push("%" + params.subtitle + "%");
  }
  if (params.startDate) {
    sql += " and createTime>=?"
    selectParams.push(params.startDate);
  }
  if (params.endDate) {
    sql += " and createTime<=?"
    selectParams.push(params.endDate);
  }

  sql += " order by createTime desc";//排序为按照创建时间倒叙

  if (params.pageNum && params.pageSize) {//开启分页
    let current = params.pageNum;//当前页码
    let pageSize = params.pageSize;//一页展示多少条数据
    sql += " limit ?,? ";
    selectParams.push((current - 1) * pageSize, parseInt(pageSize));
  }

  if (params.maintitle) {
    sql2 += " and maintitle like ?";
    selectParams.push("%" + params.maintitle + "%");
  }
  if (params.subtitle) {
    sql2 += " and subtitle like ?";
    selectParams.push("%" + params.subtitle + "%");
  }

  if (params.startDate) {
    sql += " and createTime>=?"
    selectParams.push(params.startDate);
  }
  if (params.endDate) {
    sql += " and createTime<=?"
    selectParams.push(params.endDate);
  }

  pool.query(sql + ";" + sql2, selectParams, function (err, result) {
    jsonWrite(res, result, err);
  })
})

// 获取新闻列表
router.post("/getNewsDetails", (req, res) => {
  let sql = $sql.projectSql.getNewsDetails;

  let params = { ...req.body };
  let selectParams = [
    params.id
  ];

  pool.query(sql, selectParams, function (err, result) {
    jsonWrite(res, result, err);
  })
})

// 新增新闻列表
router.post("/addNews", (req, res) => {
  let sql = $sql.projectSql.addNews;
  let params = { ...req.body };
  let addParams = [
    params.maintitle,
    params.subtitle,
    params.maintext
  ]
  pool.query(sql, addParams, function (err, result) {
    jsonWrite(res, result, err);
  })
})
// 修改新闻列表
router.post("/updateNews", (req, res) => {
  let sql = $sql.projectSql.updateNews;

  let params = { ...req.body };
  let updateParams = [
    params.maintitle,
    params.subtitle,
    params.maintext,
    params.id
  ]
  pool.query(sql, updateParams, function (err, result) {
    jsonWrite(res, result, err);
  })
})

// 删除新闻列表
router.post("/deleteNews", (req, res) => {
  let sql = $sql.projectSql.deleteNews;

  let params = { ...req.body };
  let deleteParams = [
    params.id
  ]
  pool.query(sql, deleteParams, function (err, result) {
    jsonWrite(res, result, err);
  })
})
module.exports = router